package com.trainingcloud.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Vector;

import com.trainingcloud.entity.ClientDocument;
import com.trainingcloud.entity.Document;
import com.trainingcloud.entity.Role;
import com.trainingcloud.entity.User;
import com.trainingcloud.exception.DAOException;

public class DocumentDAO extends BaseDAO
{
	private void fillDocument(ResultSet rs, Document document) throws SQLException, DAOException
	{
		document.setId(rs.getInt("id"));
		document.setTitle(rs.getString("title"));
		document.setFilename(rs.getString("filename"));
		document.setMimeType(rs.getString("mime_type"));
		document.setFileLocation(rs.getString("file_location"));
		document.setShared(rs.getBoolean("shared"));
		UserDAO userDAO = new UserDAO();
		document.setOwner(userDAO.getUserById(rs.getInt("owner_user_id")));
		document.setCreateTime(new Date(rs.getTimestamp("create_time").getTime()));
		if(rs.getTimestamp("shared_time")!=null)
		{
			document.setSharedTime(new Date(rs.getTimestamp("shared_time").getTime()));
		}
	}
	
	private void fillClientDocument(ResultSet rs, ClientDocument clientDocument) throws SQLException, DAOException
	{
		Document document = new Document();
		fillDocument(rs, document);
		clientDocument.setDocument(document);
		clientDocument.setFavorite(rs.getBoolean("favorited"));
		if(rs.getTimestamp("used_time")!=null)
			clientDocument.setLatestUsedTime(new Date(rs.getTimestamp("used_time").getTime()));
	}
	
	public Document getDocumentById(int id) throws DAOException
	{
		Document document = null;
		
		Connection connection = getConnection();
		try 
		{
			PreparedStatement query = connection.prepareStatement("SELECT * FROM t_document WHERE id=?");
			query.setInt(1, id);
			ResultSet rs= query.executeQuery();
			if(rs.next())
			{
				document = new Document();
				fillDocument(rs, document);
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
		
		return document;
	}
	
	public Vector<ClientDocument> searchDocuments(int userId, String text) throws DAOException
	{
		Vector<ClientDocument> documents = new Vector<ClientDocument>();
		
		Connection connection = getConnection();
		try 
		{
			PreparedStatement query = connection.prepareStatement("SELECT D.*, H.used_time " +
																  "FROM (" + 
																  "    SELECT A.*, " +
																  "           CASE B.user_id is NULL WHEN TRUE THEN FALSE ELSE TRUE END as favorited " +
																  "    FROM   (SELECT * FROM t_document WHERE (owner_user_id=? OR shared=1) AND title like '%"+text+"%') A " +
																  "    LEFT OUTER JOIN " +
																  "           (SELECT * FROM t_favorite WHERE user_id=?) B " +
																  "    ON A.id=B.document_id) D " +
																  "LEFT OUTER JOIN " + 
																  "     (SELECT * FROM t_used_history WHERE user_id=?) H " +
																  "ON D.id=H.document_id " +
																  "ORDER BY D.id");
			query.setInt(1, userId);
			query.setInt(2, userId);
			query.setInt(3, userId);
			ResultSet rs= query.executeQuery();
			while(rs.next())
			{
				ClientDocument document = new ClientDocument();
				fillClientDocument(rs, document);
				documents.add(document);
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
		
		return documents;
	}
	
	public Vector<ClientDocument> getAllDocumentsByOwner(int ownerUserId) throws DAOException
	{
		Vector<ClientDocument> documents = new Vector<ClientDocument>();
		
		Connection connection = getConnection();
		try 
		{
			PreparedStatement query = connection.prepareStatement("SELECT D.*, H.used_time " +
																  "FROM (" + 
																  "    SELECT A.*, " +
																  "           CASE B.user_id is NULL WHEN TRUE THEN FALSE ELSE TRUE END as favorited " +
																  "    FROM   (SELECT * FROM t_document WHERE owner_user_id=?) A " +
																  "    LEFT OUTER JOIN " +
																  "           (SELECT * FROM t_favorite WHERE user_id=?) B " +
																  "    ON A.id=B.document_id) D " +
																  "LEFT OUTER JOIN " + 
																  "     (SELECT * FROM t_used_history WHERE user_id=?) H " +
																  "ON D.id=H.document_id " +
																  "ORDER BY D.id");
			query.setInt(1, ownerUserId);
			query.setInt(2, ownerUserId);
			query.setInt(3, ownerUserId);
			ResultSet rs= query.executeQuery();
			while(rs.next())
			{
				ClientDocument document = new ClientDocument();
				fillClientDocument(rs, document);
				documents.add(document);
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
		
		return documents;
	}
	
	public Vector<ClientDocument> getFavoriteDocuments(int userId) throws DAOException
	{
		Vector<ClientDocument> documents = new Vector<ClientDocument>();
		
		Connection connection = getConnection();
		try 
		{
			PreparedStatement query = connection.prepareStatement("SELECT D.*, H.used_time " +
																  "FROM (" + 
																  "    SELECT A.*, " +
																  "           TRUE as favorited " +
																  "    FROM   t_document A " +
																  "    INNER JOIN " +
																  "           t_favorite B " +
																  "    ON B.user_id=? AND " +
																  "       A.id=B.document_id) D " +
																  "LEFT OUTER JOIN " + 
																  "     (SELECT * FROM t_used_history WHERE user_id=?) H " +
																  "ON D.id=H.document_id " +
																  "ORDER BY D.id");
			query.setInt(1, userId);
			query.setInt(2, userId);
			ResultSet rs= query.executeQuery();
			while(rs.next())
			{
				ClientDocument document = new ClientDocument();
				fillClientDocument(rs, document);
				documents.add(document);
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
		
		return documents;
	}
	
	public Vector<ClientDocument> getLatestUsedDocuments(int userId, int count) throws DAOException
	{
		Vector<ClientDocument> documents = new Vector<ClientDocument>();
		
		Connection connection = getConnection();
		try 
		{
			PreparedStatement query = connection.prepareStatement("SELECT D.*, " +
																  "       CASE F.user_id is NULL WHEN TRUE THEN FALSE ELSE TRUE END as favorited " +
																  "FROM (" + 
																  "    SELECT A.*, " +
																  "           B.used_time " +
																  "    FROM   t_document A " +
																  "    INNER JOIN " +
																  "           t_used_history B " +
																  "    ON B.user_id=? AND " +
																  "       A.id=B.document_id) D " +
																  "LEFT OUTER JOIN " + 
																  "     (SELECT * FROM t_favorite WHERE user_id=?) F " +
																  "ON D.id=F.document_id " +
																  "ORDER BY D.id " +
																  "LIMIT ?");
			query.setInt(1, userId);
			query.setInt(2, userId);
			query.setInt(3, count);
			ResultSet rs= query.executeQuery();
			while(rs.next())
			{
				ClientDocument document = new ClientDocument();
				fillClientDocument(rs, document);
				documents.add(document);
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
		
		return documents;
	}
	
	public Vector<ClientDocument> getAllSharedDocuments(int userId) throws DAOException
	{
		Vector<ClientDocument> documents = new Vector<ClientDocument>();
		
		Connection connection = getConnection();
		try 
		{
			PreparedStatement query = connection.prepareStatement("SELECT D.*, H.used_time " +
																  "FROM (" + 
																  "    SELECT A.*, " +
																  "           CASE B.user_id is NULL WHEN TRUE THEN FALSE ELSE TRUE END as favorited " +
																  "    FROM   (SELECT * FROM t_document WHERE shared=?) A " +
																  "    LEFT OUTER JOIN " +
																  "           (SELECT * FROM t_favorite WHERE user_id=?) B " +
																  "    ON A.id=B.document_id) D " +
																  "LEFT OUTER JOIN " + 
																  "     (SELECT * FROM t_used_history WHERE user_id=?) H " +
																  "ON D.id=H.document_id " +
																  "ORDER BY D.id");
			query.setBoolean(1, true);
			query.setInt(2, userId);
			query.setInt(3, userId);
			ResultSet rs= query.executeQuery();
			while(rs.next())
			{
				ClientDocument document = new ClientDocument();
				fillClientDocument(rs, document);
				documents.add(document);
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
		
		return documents;
	}
	
	public void insertDocument(Document document) throws DAOException
	{
		Connection connection = getConnection();
		try 
		{
			PreparedStatement query = connection.prepareStatement("INSERT INTO t_document (title, filename, mime_type, file_location, shared, owner_user_id, create_time, shared_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
			query.setString(1, document.getTitle());
			query.setString(2, document.getFilename());
			query.setString(3, document.getMimeType());
			query.setString(4, document.getFileLocation());
			query.setBoolean(5, document.isShared());
			query.setInt(6, document.getOwner().getId());
			query.setTimestamp(7, new java.sql.Timestamp(document.getCreateTime().getTime()));
			if(document.getSharedTime()!=null)
				query.setTimestamp(8, new java.sql.Timestamp(document.getSharedTime().getTime()));
			else
				query.setTimestamp(8, null);
			if(!query.execute() && query.getUpdateCount()==1)
			{
				query = connection.prepareStatement("select @@IDENTITY");
				ResultSet rs = query.executeQuery();
				if(rs.next())
				{
					document.setId(rs.getInt(1));
				}
				else
				{
					throw new DAOException("Get last insert document id failed.");
				}
			}
			else
			{
				throw new DAOException("Insert into t_document failed.");
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
	}
	
	public void updateDocument(Document document) throws DAOException
	{
		Connection connection = getConnection();
		try
		{
			PreparedStatement query = connection.prepareStatement("UPDATE t_document SET title=?, filename=?, mime_type=?, file_location=?, shared=?, shared_time=? WHERE id=?");
			query.setString(1, document.getTitle());
			query.setString(2, document.getFilename());
			query.setString(3, document.getMimeType());
			query.setString(4, document.getFileLocation());
			query.setBoolean(5, document.isShared());
			if(document.getSharedTime()!=null)
				query.setTimestamp(6, new java.sql.Timestamp(document.getSharedTime().getTime()));
			else
				query.setTimestamp(6, null);
			query.setInt(7, document.getId());
			if(!query.execute() && query.getUpdateCount()==1)
			{
				// Success
			}
			else
			{
				throw new DAOException("Update t_document failed.");
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
	}
	
	public void deleteDocument(int documentId) throws DAOException
	{
		Connection connection = getConnection();
		try
		{
			PreparedStatement query = connection.prepareStatement("DELETE FROM t_favorite WHERE document_id=?");
			query.setInt(1, documentId);
			query.execute();
			
			query = connection.prepareStatement("DELETE FROM t_used_history WHERE document_id=?");
			query.setInt(1, documentId);
			query.execute();
			
			query = connection.prepareStatement("DELETE FROM t_document WHERE id=?");
			query.setInt(1, documentId);
			if(!query.execute() && query.getUpdateCount()==1)
			{
				// Success
			}
			else
			{
				throw new DAOException("Delete from t_document failed.");
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
	}
}
